﻿using Dapper;
using Microsoft.Data.Sqlite;
using System;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Shapes;

namespace SqliteCreateWithPassword
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        static SqliteConnection conn = null;

        public MainWindow()
        {
            InitializeComponent();
        }

        private void btn_selectPath_Click(object sender, RoutedEventArgs e)
        {
            var dialog = new System.Windows.Forms.FolderBrowserDialog();
            dialog.SelectedPath = @"C:\Users\Administrator\Desktop";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txt_path.Text = dialog.SelectedPath;
                ShowInfo("数据库路径已设置为：" + dialog.SelectedPath);
            }
        }

        private void getConn()
        {
            string dbpath = txt_path.Text;
            string dbname = txt_dbname.Text;
            string pwd = txt_pwd.Text;

            string dbFullPath = System.IO.Path.Combine(dbpath, dbname); // 组合文件路径
            string connString = new SqliteConnectionStringBuilder
            {
                DataSource = dbFullPath,
                Mode = SqliteOpenMode.ReadWriteCreate,
                Password = pwd
            }.ToString();

            if (conn != null)
            {
                conn.Dispose();
            }
            conn = new SqliteConnection(connString);
        }

        private void btn_createdb_Click(object sender, RoutedEventArgs e)
        {
            if (string.IsNullOrEmpty(txt_dbname.Text) || string.IsNullOrEmpty(txt_pwd.Text))
            {
                MessageBox.Show("请输入数据库名和密码");
                return;
            }

            string dbFullPath = System.IO.Path.Combine(txt_path.Text, txt_dbname.Text);
            ShowInfo(dbFullPath);
            if (File.Exists(dbFullPath))
            {
                MessageBox.Show("该数据库已存在，不能创建");
                return;
            }

            getConn();
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();

                var createTable = @"CREATE TABLE IF NOT EXISTS ""User"" (""Id"" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ""name"" TEXT, ""age"" INTEGER);";
                conn.Execute(createTable);
                conn.Close();

                ShowInfo($"数据库: {txt_path.Text}{txt_dbname.Text} 已创建成功，并插入了测试表 User");
            }
        }

        private void btn_insert_Click(object sender, RoutedEventArgs e)
        {
            getConn();
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
                var name = GenerateSurname();
                var age = GenerateAge();
                var insertSql = $@"INSERT INTO ""User"" (name, age) VALUES (""{name}"", {age});";
                conn.Execute(insertSql);
                conn.Close();
                ShowInfo($"插入记录成功==> Name: {name} Age: {age}");
            }
        }

        private void btn_query_Click(object sender, RoutedEventArgs e)
        {
            getConn();
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();

                var selectSql = "SELECT id, name, age FROM User;";
                var users = conn.Query<User>(selectSql).ToList();

                ShowInfo($"================ 查询 db[{txt_dbname.Text}] 到以下记录：===================");
                foreach (var user in users)
                {
                    ShowInfo($"Id: {user.Id} Name: {user.Name} Age: {user.Age}");
                }

                ShowInfo($"=============== 共 {users.Count} 条 ================");
                conn.Close();
            }
        }

        private static int GenerateAge()
        {
            return new Random().Next(1, 150);
        }

        private static string GenerateSurname()
        {
            
            return "Sam";
        }

        public void ShowInfo(string info)
        {
            txtInfo.AppendText(info + "\n");
            txtInfo.ScrollToEnd();
        }
    }

    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
}
